package com.module.product.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import org.apache.commons.lang.StringUtils;
import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.Transaction;

import base.common.CUDEnum;
import base.dao.GenericHibernateDao;
import base.util.Page;

import com.module.product.vo.AttrItem;
/**
*
* 商品属性项dao类
*
* @author  王勇云
* @version  [V1.0, Jul 22, 2011]
* @see  [相关类/方法]
* @since  [二代服务平台/商品属性项]
*/
public class AttrItemDao extends
		GenericHibernateDao<AttrItem, String> {
	private static final AttrItemDao instance = new AttrItemDao();

	private AttrItemDao() {
	}

	public synchronized static AttrItemDao getInstance() {
		return instance;
	}
	   /**
     * <逻辑删除>
     * <功能详细描述>
     * @param attrGroup
     * @return
     * @see [类、类#方法、类#成员]
     */
    public boolean deleteObjs(Session session,AttrItem obj){
        Transaction tx = null;
        boolean flag = false;
        try {
            String hql = "update AttrItem set updaterId = ?,updateTime=?,status =? where id in ("+obj.getId()+")";
            Query query  = session.createQuery(hql); 
                  query.setParameter(0,obj.getUpdaterId());
                  query.setParameter(1, new Date());
                  query.setParameter(2, CUDEnum.D.toString());
                  int i  = query.executeUpdate();
            flag = true;
            return flag;
        } catch (Exception ex) {
            ex.printStackTrace();
            return flag;
        } 
    }
    public Page getList(Session session,AttrItem obj,Page page){
        String hql1 = "from AttrItem as obj where obj.status  !='D' ";
        String hql2 = "select count(*) from AttrItem as obj  where obj.status !='D' ";
        if(StringUtils.isNotBlank(obj.getAttrGroup())){
            hql1=hql1 +  " and obj.attrGroup = '"+obj.getAttrGroup()+"'";
            hql2=hql2 +  " and obj.attrGroup = '"+obj.getAttrGroup()+"'";
        }
        Query query = session.createQuery(hql1);
              query.setFirstResult(page.getStart()).setMaxResults(page.getLimit());
        
        page.setTotal((long) ((Long) session.createQuery(hql2)
            .uniqueResult()).intValue());
        page.setRows(query.list());
        return page;
    } 
    
    
    public List<AttrItem> listByGroupId(Session session,String groupId){
        List<AttrItem> list  = new ArrayList<AttrItem>();
        String hql = "from AttrItem as obj where obj.status  !=? and obj.attrGroup = ? ";
        Query query = session.createQuery(hql);
        query.setParameter(0,CUDEnum.D.toString());
        query.setParameter(1,groupId);
        list = query.list();
        return list;
    }
    
    
    public List<AttrItem> listByGroupId(Connection conn,String attrGroupId,String productId) throws SQLException{
        List<AttrItem> list  = new ArrayList<AttrItem>();
        AttrItem attr = null;
        PreparedStatement stmt = null;
        try
        {
        String sql = "select a.c_id,a.c_name,b.c_attr_value ,a.c_groupId,a.c_desc ,b.c_product_id,a.c_type,b.i_attr_value_size from p_attr_item a left join p_attr_value b on a.c_id = b.c_attritem_id  and b.c_product_id =? where a.c_status !=? and a.c_groupId =? ";
        stmt = conn.prepareStatement(sql);
                          stmt.setString(1,productId);
                          stmt.setString(2,CUDEnum.D.toString());
                          stmt.setString(3,attrGroupId);
        ResultSet rs  = stmt.executeQuery();  
        
        while(rs.next()){ 
            attr = new AttrItem();
            attr.setId(rs.getString(1));
            attr.setName(rs.getString(2));
            attr.setAttrValue(rs.getString(3));
            attr.setAttrGroup(rs.getString(4));
            attr.setDesc(rs.getString(5));
            attr.setType(rs.getString(7));
            attr.setAttrValueSize(rs.getInt(8));
            list.add(attr);
           }                  
        } finally{
        	this.close(stmt);
        }
        return list;
    }
    
    public List<AttrItem> listByCategoryId(Connection conn,String categoryId) throws SQLException{
        List<AttrItem> list  = new ArrayList<AttrItem>();
        AttrItem attr = null;
        PreparedStatement stmt = null;
        try
        {
        String sql = "select a.c_id,a.c_name ,a.c_groupId,a.c_desc ,a.c_type from p_attr_item a " +
        		" where a.c_status !=? and a.c_groupId in (select c_id from p_attr_group where c_category_id = ? )";
        stmt = conn.prepareStatement(sql);
                          stmt.setString(1,CUDEnum.D.toString());
                          stmt.setString(2,categoryId);
        ResultSet rs  = stmt.executeQuery();        
        while(rs.next()){ 
            attr = new AttrItem();
            attr.setId(rs.getString(1));
            attr.setName(rs.getString(2));
            attr.setAttrGroup(rs.getString(3));
            attr.setDesc(rs.getString(4));
            attr.setType(rs.getString(5));
            list.add(attr);
           }                  
        } finally{
        	this.close(stmt);
        }
        return list;
    }
    
    public boolean deleteAllAttrValuebyProductId(Session session,String productId){
        boolean b = false;
        Transaction tx = null;
        try
        {
            tx = session.beginTransaction();
            String sql = " delete from  p_attr_value  where  c_product_id =? ";
            Connection conn = session.connection();
            PreparedStatement stmt = conn.prepareStatement(sql);
                              stmt.setString(1,productId);
            stmt.executeUpdate();
            tx.commit();
            b = true ;
        }
        catch (SQLException e)
        {
            e.printStackTrace();
        } 
        session.close();
        return b;
    }

    public String findAttrNumbyGroupId(Connection conn,String attrGroupId) throws SQLException{
        String num = "0";
        Transaction tx = null;
        PreparedStatement stmt=null;
        try
        {
        String sql = "select count(*) from p_attr_item where c_groupId = ? ";
        stmt = conn.prepareStatement(sql);
                          stmt.setString(1,attrGroupId);
        ResultSet rs  = stmt.executeQuery();  
        while(rs.next()){ 
            num = rs.getString(1);
           }                  
        }finally{
        	this.close(stmt);
        }
        return num;
    }
    
}
